package no.uib.hplims.tools;

import no.uib.hplims.models.MyOrder;
import no.uib.hplims.models.Peptide;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.vaadin.appfoundation.persistence.facade.FacadeFactory;

//~--- JDK imports ------------------------------------------------------------

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * Class to import data from Excel-files provided by Thermo Fisher describing
 * the peptides bound to a specific order.
 * 
 * @author John
 * 
 */
public class ExcelReader {

	private int positionNumber = -1;
	private String wellID = null;
	private String barcodeID = null;
	private String peptideName = null;
	private String nTerminal = null;
	private String cTerminal = null;
	private String sequence = null;
	private double molecularWeight = -1;
	private double amountInMg = -1;

	/**
	 * Default constructor
	 */
	public ExcelReader() {

	}

	/**
	 * Imports data from an .xls file provided by Thermo Fisher, and updates the
	 * peptides in an order with data from this file.
	 * 
	 * @param order
	 *            The order this file belongs to
	 * @param file
	 *            The file to import data from
	 * @return True if successful, false otherwise
	 */
	public boolean startImport(MyOrder order, File file) {
		InputStream inputStream = null;

		try {
			inputStream = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			System.out.println("File not found in the specified path.");
			e.printStackTrace();
		}

		POIFSFileSystem fileSystem = null;

		try {
			fileSystem = new POIFSFileSystem(inputStream);

			HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
			HSSFSheet sheet = workBook.getSheetAt(0);

			Iterator<Row> rows = sheet.rowIterator();

			while (rows.hasNext()) {
				HSSFRow row = (HSSFRow) rows.next();

				// Check the first rows
				if (row.getRowNum() < 3) {
					// If the row contains a String value
					if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING) {
						// Check if the String is Sales Ordercode
						if (row.getCell(0).getStringCellValue()
								.equalsIgnoreCase("Sales Ordercode")
								|| row.getCell(0).getStringCellValue()
										.equalsIgnoreCase("Sales Ordercode:")) {
							// If this row contains Sales Ordercode, extract it
							// and set it and save order
							HSSFCell cell = row.getCell(3);
							if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
								String so = cell.getStringCellValue();
								setSalesOrderCode(order, so);
								FacadeFactory.getFacade().store(order);
							}
						}
					}
					continue;
				} else if (row.getRowNum() == 3) {
					checkRowHeaders(row);
					continue;
				}

				// display row number in the console.
				// System.out.println("Row No.: " + row.getRowNum());

				// iterate through cells on the row
				Iterator<Cell> cells = row.cellIterator();
				while (cells.hasNext()) {
					HSSFCell cell = (HSSFCell) cells.next();

					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_NUMERIC: {
						// cell type numeric.
						// System.out.println("Numeric value: "
						// + cell.getNumericCellValue());
						if (cell.getColumnIndex() == 0) {
							positionNumber = (int) cell.getNumericCellValue();
						} else if (cell.getColumnIndex() == 7) {
							molecularWeight = cell.getNumericCellValue();
						} else if (cell.getColumnIndex() == 8) {
							amountInMg = cell.getNumericCellValue();
						}
						break;
					}
					case HSSFCell.CELL_TYPE_STRING: {
						// cell type string.
						HSSFRichTextString richTextString = cell
								.getRichStringCellValue();
						String value = richTextString.getString();
						// System.out.println("String value: "
						// + richTextString.getString());
						if (cell.getColumnIndex() == 1) {
							wellID = value;
						} else if (cell.getColumnIndex() == 2) {
							barcodeID = value;
						} else if (cell.getColumnIndex() == 3) {
							peptideName = value;
						} else if (cell.getColumnIndex() == 4) {
							nTerminal = value;
						} else if (cell.getColumnIndex() == 5) {
							sequence = value;
						} else if (cell.getColumnIndex() == 6) {
							cTerminal = value;
						}
						break;
					}
					default: {
						// types other than String and Numeric.
						break;
					}
					}

				}
				// System.out.println(positionNumber + " " + wellID + " "
				// + barcodeID + " " + peptideName + " " + nTerminal + " "
				// + sequence + " " + cTerminal + " " + molecularWeight);
				if (allFieldsInitialized()) {
					if (sequence.contains("[Arg(13C6;15N4)]")) {
						sequence = sequence.replace("[Arg(13C6;15N4)]", "R");
					} else if (sequence.contains("[Lys(13C6; 15N2)]")) {
						sequence = sequence.replace("[Lys(13C6; 15N2)]", "K");
					} else if (sequence.contains("[Val(13C5; 15N)]")) {
						sequence = sequence.replace("[Val(13C5; 15N)]", "V");
					}
					Peptide p = findPeptide(order, sequence);
					updatePeptide(p);
				}
				resetVars();
			}

			// close inputstream when finished
			if (inputStream != null) {
				inputStream.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * If a peptide was found, update the imported fields
	 * 
	 * @param p
	 *            The peptide to update
	 */
	private void updatePeptide(Peptide p) {
		if (p != null) {
			if (positionNumber != -1) {
				p.setPositionNumber(positionNumber);
			}
			if (wellID != null) {
				p.setWellID(wellID);
			}
			if (barcodeID != null) {
				p.setBarcodeID(barcodeID);
			}
			if (nTerminal != null) {
				p.setnTerminal(nTerminal);
			}
			if (cTerminal != null) {
				p.setcTerminal(cTerminal);
			}
			if (molecularWeight != -1) {
				p.setMolecularWeight(molecularWeight);
			}
			if (amountInMg != -1) {
				p.setAmountInMg(amountInMg);
			}
			FacadeFactory.getFacade().store(p);
		}
	}

	private void setSalesOrderCode(MyOrder order, String so) {
		order.setSalesOrderCode(so);
	}

	/**
	 * Finds the peptide in this order with the given sequence
	 * 
	 * @param order
	 *            The order the peptide belongs to
	 * @param seq
	 *            The sequence of the peptide
	 * @return The peptide
	 */
	private Peptide findPeptide(MyOrder order, String seq) {
		String query = "SELECT p FROM Peptide AS p WHERE p.peptideSequence=:sequence AND p.belongsToOrder=:order";

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("sequence", seq);
		parameters.put("order", order);

		Peptide peptide = FacadeFactory.getFacade().find(query, parameters);

		if (peptide != null) {
			System.out.println("Peptide: " + peptide.getPeptideSequence());
		} else {
			System.out.println("No result found for sequence: " + seq);
		}

		return peptide;
	}

	/**
	 * Checks if all fields have been initialized.
	 * 
	 * @return true if all fields have been initialized properly
	 */
	private boolean allFieldsInitialized() {
		if ((positionNumber != -1) && (wellID != null) && (barcodeID != null)
				&& (nTerminal != null) && (cTerminal != null)
				&& (molecularWeight != -1) && (amountInMg != -1)) {
			return true;
		}
		return false;
	}

	/**
	 * Checks if the headers are on the right format. TODO: Improve header
	 * checking. Possibly make more dynamic by introducing indices for columns.
	 * 
	 * @param row
	 *            The header row
	 */
	private void checkRowHeaders(Row row) {
		if (row.getPhysicalNumberOfCells() != 9) {
			System.out.println("Wrong number of header rows");
		}
		if (!row.getCell(0).getStringCellValue().trim()
				.equalsIgnoreCase("Posno")) {
			System.out.println("First cell should be Posno");
		}
		if (!row.getCell(1).getStringCellValue().trim()
				.equalsIgnoreCase("Well_ID")) {
			System.out.println("Second cell should be WellID");
		}
		if (!row.getCell(2).getStringCellValue().trim()
				.equalsIgnoreCase("BarcodeID")) {
			System.out.println("Third cell should be BarcodeID");
		}
		if (!row.getCell(3).getStringCellValue().trim()
				.equalsIgnoreCase("Peptide Name")) {
			System.out.println("Fourth cell should be Peptide Name");
		}
		if (!row.getCell(4).getStringCellValue().trim()
				.equalsIgnoreCase("Nterm")) {
			System.out.println("Fifth cell should be N terminal");
		}
		if (!row.getCell(5).getStringCellValue().trim()
				.equalsIgnoreCase("Sequence")) {
			System.out.println("Sixth cell should be Sequence");
		}
		if (!row.getCell(6).getStringCellValue().trim()
				.equalsIgnoreCase("CTerm")) {
			System.out.println("Seventh cell should be C terminal");
		}
		if (!row.getCell(7).getStringCellValue().trim().equalsIgnoreCase("MW")) {
			System.out.println("Eight cell should be Molecular weight");
		}
		if (!row.getCell(8).getStringCellValue().trim()
				.equalsIgnoreCase("Amount [mg]")) {
			System.out.println("Ninth cell should be Amount in mg");
		}
	}

	/**
	 * Resets the variables extracted from file between each iteration. Helps
	 * when checking for errors in {@link #allFieldsInitialized()}.
	 */
	private void resetVars() {
		positionNumber = -1;
		wellID = null;
		barcodeID = null;
		peptideName = null;
		nTerminal = null;
		cTerminal = null;
		sequence = null;
		molecularWeight = -1;
		amountInMg = -1;
	}

}
